﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace CodeSystem
{
    public class SQLHelper
    {
        /// <summary>
        /// 链接字符串
        /// </summary>
        public static string str = @"server=10.0.11.69;database=QMDB;uid=sa;pwd=HBIShg2016";

        /// <summary>
        /// 返回几行受影响，不执行事务
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parm">参数数组</param>
        /// <param name="type">执行类型，普通语句或者存储过程</param>
        /// <returns>返回int类型</returns>
        public static int ExcuteNonQuery(string sql, SqlParameter[] parm = null, CommandType type = CommandType.Text)
        {
            using (SqlConnection conn = new SqlConnection(str))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (parm != null)
                        cmd.Parameters.AddRange(parm);
                    cmd.CommandType = type;
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }


        /// <summary>
        /// 返回几行受影响,专门执行事务
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parm">参数数组</param>
        /// <returns>返回int类型</returns>
        public static int ExcuteNonQueryWithTransaction(string sql, SqlParameter[] parm = null)
        {
            SqlConnection conn = new SqlConnection(str);
            SqlCommand cmd = new SqlCommand(sql, conn);
            int Resut = 0;
            SqlTransaction ran = null;
            try
            {
                conn.Open();
                ran = conn.BeginTransaction();
                cmd.Transaction = ran;
                Resut = cmd.ExecuteNonQuery();
                ran.Commit();
            }
            catch (Exception ex)
            {
                ran.Rollback();
                
                return 0;
            }
            finally
            {
                conn.Close();
            }
            return Resut;
        }


        /// <summary>
        /// 返回数据Reader
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parm">参数数组</param>
        /// <param name="type">执行类型，普通语句或者存储过程</param>
        /// <returns>SqlDataReader类型</returns>
        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] parm = null, CommandType type = CommandType.Text)
        {
            SqlConnection conn = new SqlConnection(str);
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (parm != null)
                cmd.Parameters.AddRange(parm);
            cmd.CommandType = type;
            SqlDataReader Reader = null;
            try
            {
                conn.Open();
                Reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                return null;
            }
            return Reader;
        }


        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parm">参数数组</param>
        /// <param name="type">执行类型</param>
        /// <returns>object类型</returns>
        public static object ExecuteScalar(string sql, SqlParameter[] parm = null, CommandType type = CommandType.Text)
        {
            using (SqlConnection conn = new SqlConnection(str))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (parm != null)
                        cmd.Parameters.AddRange(parm);
                    cmd.CommandType = type;
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }


        /// <summary>
        /// 返回DataSet数据仓库
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parm">参数数组</param>
        /// <param name="type">执行类型</param>
        /// <returns>DataSet数据</returns>
        public static DataSet ExcuteDateSet(string sql, SqlParameter[] parm = null, CommandType type = CommandType.Text)
        {
            DataSet set = new DataSet();
            using (SqlConnection conn = new SqlConnection(str))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (parm != null)
                    cmd.Parameters.AddRange(parm);
                cmd.CommandType = type;
                SqlDataAdapter abp = new SqlDataAdapter();
                abp.SelectCommand = cmd;
                abp.Fill(set);
                return set;
            }
        }

        public static DataTable GetTable(string sql, SqlParameter[] parm = null, CommandType type = CommandType.Text)
        {
            DataSet set = new DataSet();
            using (SqlConnection conn = new SqlConnection(str))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (parm != null)
                    cmd.Parameters.AddRange(parm);
                cmd.CommandType = type;
                SqlDataAdapter abp = new SqlDataAdapter();
                abp.SelectCommand = cmd;
                abp.Fill(set);
                return set.Tables[0];
            }
        }
    }
}
